import xlrd

def agg(d,n):
    # setdefault设置默认或取已经有的值
    d[n] = (d.setdefault(n,0)+1)

def aggdata(datas):
    dred = {}
    dblue = {}
    for i in datas:
        for j in range(0,6):
            agg(dred,i[j])
        agg(dblue,i[6])
    return (dred,dblue)

def sort_key(two_tuple):
    return two_tuple[1]

def sort_ordi(two_tuple):
    return two_tuple[0]

def count_sort(dball,kef):
    nums = [(i,dball.get(i)) for i in dball.keys()]
    nums.sort(key=kef)
    return nums


def judgemyticket(): 
    mynums = [[7,14,19,24,27,31,16],[1,6,14,22,26,32,1],[21,23,24,28,31,33,8]]

    filepath = "D:\\tempCanDel\\ssq.xls"
    f = xlrd.open_workbook(filepath)
    s = f.sheets()[0]
    datas = []


    for i in range(2,s.nrows):
        datas.append(s.row_values(i,2,9))

    a = 0;
    for i in mynums:
        try:
            print(str(i)+" in row :"+str(datas.index(i)))
            a=a+1
        except Exception:
            None
    print("finall in date :"+str(s.row_values(s.nrows-1,0,2)))
    print('hit first '+str(a))
    print("down")


if __name__ == '__main__':
    filepath = "D:\\tempCanDel\\ssq.xls"
    f = xlrd.open_workbook(filepath)
    s = f.sheets()[0]
    datas = []
    for i in range(2,s.nrows):
        datas.append(s.row_values(i,2,9))
    (rb,bb) = aggdata(datas)
    
    print("red ball:"+str(count_sort(rb,sort_key)))
    
    print("blue ball:"+str(count_sort(bb,sort_key)))

    print("red ball:"+str(count_sort(rb,sort_ordi)))
    print("blue ball:"+str(count_sort(bb,sort_ordi)))
    judgemyticket()

